/*
 * @Date: 2019-06-02 15:16:02
 * @Author: Ryan Huang
 * @Description:
 */
package db

import (
	"database/sql"
	"fmt"
	"my/homework/chatroom1/constants"
	"my/homework/chatroom1/libs"
	"strings"

	_ "github.com/go-sql-driver/mysql"
)

type ARDB struct {
	db *sql.DB
	condition
}

type condition struct {
	table     string
	selectRaw string
	where     string
	groupby   string
	limit     string
	having    string
	join      string
}

var mapSequence []string //insert的时候，问号绑定参数会乱，用mapSequence来规范参数顺序

var Driver = constants.Driver
var DNS = constants.DNS

func ResolveDB(driver, dns string) *ARDB {
	if driver == "" {
		driver = Driver
	}
	if dns == "" {
		dns = DNS
	}
	db, _ := sql.Open(driver, dns)
	condition := condition{}
	return &ARDB{db, condition}
}

func (ardb *ARDB) SetTable(table string) *ARDB {
	ardb.condition.table = table
	return ardb
}

func (ardb *ARDB) SelectRaw(selectRaw string) *ARDB {
	ardb.condition.selectRaw = selectRaw
	return ardb
}

func (ardb *ARDB) Where(whereRaw string) *ARDB {
	ardb.condition.where = whereRaw
	return ardb
}

func (ardb *ARDB) GroupBy(groupby string) *ARDB {
	ardb.condition.groupby = groupby
	return ardb
}

func (ardb *ARDB) Limit(limit string) *ARDB {
	ardb.condition.limit = limit
	return ardb
}

func (ardb *ARDB) Having(having string) *ARDB {
	ardb.condition.having = having
	return ardb
}

func (ardb *ARDB) Join(join string) *ARDB {
	ardb.condition.join = join
	return ardb
}

func (ardb *ARDB) Get(values []string) []map[string]string {
	sqlState := ardb.generateSelectSql(values)
	stmt, err := ardb.db.Prepare(sqlState)
	if err != nil {
		panic(err)
	}
	sqlValues := generateValues(values)
	Rows, err1 := stmt.Query(sqlValues...)
	if err1 != nil {
		panic(err1)
	}
	var datas []map[string]string
	datas = generateDatas(Rows)
	return datas
}

func (ardb *ARDB) Update(updates []map[string]string) int {
	sqlState := ardb.generateUpdateSql(updates)
	stmt, err := ardb.db.Prepare(sqlState)
	if err != nil {
		panic(err)
	}
	var values []string
	for _, v := range updates {
		values = append(values, v[libs.GetMapKey(v)])
	}
	sqlValues := generateValues(values)
	Result, err1 := stmt.Exec(sqlValues...)
	if err1 != nil {
		panic(err1)
	}
	rowsAffected, err2 := Result.RowsAffected()
	if err2 != nil {
		panic(err2)
	}
	return int(rowsAffected)
}

func (ardb *ARDB) Insert(inserts []map[string]string) (int, int) {
	sqlState := ardb.generateInsertSql(inserts)
	stmt, err := ardb.db.Prepare(sqlState)
	//fmt.Println(sqlState)
	if err != nil {
		panic(err)
	}
	sqlValues := generateBatchValues(inserts)
	//fmt.Println(sqlValues)
	Result, err1 := stmt.Exec(sqlValues...)
	if err1 != nil {
		panic(err1)
	}
	rowsAffected, err2 := Result.RowsAffected()
	if err2 != nil {
		panic(err2)
	}
	lastInsertId, err3 := Result.LastInsertId()
	if err3 != nil {
		panic(err3)
	}
	return int(rowsAffected), int(lastInsertId)
}

func (ardb *ARDB) First(values []string) map[string]string {
	ardb.limit = "0,1"
	datas := ardb.Get(values)
	var data map[string]string
	if len(datas) == 0 {
		return data
	}
	return datas[0]
}

func (ardb *ARDB) Exists(values []string) bool {
	ardb.limit = "0,1"
	sqlState := ardb.generateSelectSql(values)
	stmt, err := ardb.db.Prepare(sqlState)
	if err != nil {
		panic(err)
	}
	sqlValues := generateValues(values)
	Rows, err1 := stmt.Query(sqlValues...)
	if err1 != nil {
		panic(err1)
	}
	return Rows.Next()
}

func generateDatas(Rows *sql.Rows) []map[string]string {
	var datas []map[string]string
	cols, _ := Rows.Columns()
	scans := make([]interface{}, len(cols))
	vals := make([]string, len(cols))
	for i := range vals {
		scans[i] = &vals[i]
	}
	for Rows.Next() {
		err := Rows.Scan(scans...)
		if err != nil {
			panic(err)
		}
		data := make(map[string]string, len(scans))
		for key := range scans {
			data[cols[key]] = vals[key]
		}
		datas = append(datas, data)
	}
	return datas
}

func generateValues(values []string) []interface{} {
	var sqlValues []interface{}
	count := len(values)
	for i := 0; i < count; i++ {
		sqlValues = append(sqlValues, values[i])
	}
	return sqlValues
}

func generateBatchValues(inserts []map[string]string) []interface{} {
	var sqlValues []interface{}
	for _, insert := range inserts {
		for _, field := range mapSequence {
			sqlValues = append(sqlValues, insert[field])
		}
	}
	return sqlValues
}

func (ardb *ARDB) generateInsertSql(inserts []map[string]string) string {
	if ardb.condition.table == "" {
		panic("unknown table")
	}
	mapSequence = []string{}
	var fields []string
	var values string
	var fieldString string
	var valueString string
	if len(inserts) == 0 {
		panic("no inserts")
	} else {
		for k, insert := range inserts { // eg. [["a"=>1,"b"=>2],["a"=>3,"b"=>4]]
			for field := range insert {
				if k == 0 {
					fields = append(fields, field)
				}
				values = fmt.Sprintf("?,%s", values)
			}
			values = strings.Trim(values, ",")
			values = fmt.Sprintf("(%s)", values)
			valueString = fmt.Sprintf("%s,%s", values, valueString)
		}
		fieldString = strings.Join(fields, ",")
		mapSequence = fields
	}
	valueString = strings.Trim(valueString, ",")
	sqlState := fmt.Sprintf("insert into %s (%s) values %s", ardb.condition.table, fieldString, valueString)
	return sqlState
}

func (ardb *ARDB) generateUpdateSql(updates []map[string]string) string {
	if ardb.condition.table == "" {
		panic("unknown table")
	}
	var where string
	var updateStr string
	if ardb.condition.where == "" {
		where = ""
	} else {
		where = fmt.Sprint(" where ", ardb.condition.where)
	}
	if len(updates) == 0 {
		panic("no updates")
	} else {
		for _, update := range updates {
			updateStr = fmt.Sprintf("%s %s = ?,", updateStr, libs.GetMapKey(update))
		}
		updateStr = strings.TrimRight(updateStr, ",")
	}
	sqlState := fmt.Sprintf("update %s set %s %s", ardb.condition.table, updateStr, where)
	return sqlState
}

func (ardb *ARDB) generateSelectSql(values []string) string {
	if ardb.condition.table == "" {
		panic("unknown table")
	}
	var fields string
	var groupBy string
	var limit string
	var having string
	var join string
	var where string
	if ardb.condition.selectRaw != "" {
		fields = ardb.condition.selectRaw
	} else {
		fields = "*"
	}
	if ardb.condition.groupby == "" {
		groupBy = ""
	} else {
		groupBy = fmt.Sprint(" group by ", ardb.condition.groupby)
	}
	if ardb.condition.limit == "" {
		limit = ""
	} else {
		limit = fmt.Sprint(" limit ", ardb.condition.limit)
	}
	if ardb.condition.having == "" {
		having = ""
	} else {
		having = fmt.Sprint(" having ", ardb.condition.having)
	}
	if ardb.condition.join == "" {
		join = ""
	} else {
		join = fmt.Sprint(" ", ardb.condition.join)
	}
	if ardb.condition.where == "" {
		where = ""
	} else {
		where = fmt.Sprint(" where ", ardb.condition.where)
	}
	sqlState := fmt.Sprint("select ", fields, " from ", ardb.condition.table, join, where, groupBy, having, limit)
	return sqlState
}
